﻿using Dapper_Core.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Dapper_Core.Common
{
    public class SqlUtility
    {
        private static readonly string connectionString = Appsettings.SqlServerConnection01.Value;

        #region ExecuteNonQuery

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        } 

        #endregion

        #region ExecuteNonQuerySp

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <returns></returns>
        public static int ExecuteNonQuerySp(string spName)
        {
            return ExecuteNonQuerySp(spName, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameters">存储过程所需参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuerySp(string spName, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(spName, connection);
                try
                {
                    connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteScalar();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteScalarSp

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <returns></returns>
        public static object ExecuteScalarSp(string spName)
        {
            return ExecuteScalarSp(spName, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameters">存储过程所需参数</param>
        /// <returns></returns>
        public static object ExecuteScalarSp(string spName, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(spName, connection);
                try
                {
                    connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteScalar();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteDataTable

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable();
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        } 

        #endregion

        #region ExecuteDataTableSp

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTableSp(string spName)
        {
            return ExecuteDataTableSp(spName, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameters">存储过程所需参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTableSp(string spName, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(spName, connection);
                try
                {
                    connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable();
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteDataSetSp

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetSp(string spName)
        {
            return ExecuteDataSetSp(spName, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameters">存储过程所需参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetSp(string spName, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(spName, connection);
                try
                {
                    connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new SqlParameter[0];
                    foreach (SqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        public static int GetMaxId(string tableName, string columnName)
        {
            try
            {
                string sql = "select max(" + columnName + ") from " + tableName;
                object obj = ExecuteScalar(sql);
                return obj.ToString() == "" ? 1 : Convert.ToInt32(obj);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static int GetSequence(string tableName, string columnName)
        {
            try
            {
                string sql = "select max(" + columnName + ") from " + tableName;
                object obj = ExecuteScalar(sql);
                return obj.ToString() == "" ? 1 : Convert.ToInt32(obj) + 1;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 将普通字符串转换成参数化like查询条件（左右加%，对字符串中的%进行特殊处理）
        /// </summary>
        /// <param name="condition">普通字符串</param>
        /// <returns></returns>
        public static string GetLikeCondition(string condition)
        {
            if (string.IsNullOrEmpty(condition)) return "%";
            string result = condition;
            Regex reg = new Regex(" +");//去除全部空格
            if (reg.IsMatch(condition))
            {
                result = reg.Replace(result, "");
            }
            result = result.Replace("'", "''");
            result = result.Replace("[", "[[]");
            result = result.Replace("_", "[_]");
            result = result.Replace("%", "[%]");
            result = result.Replace("^", "[^]");
            return "%" + result + "%";
        }
    }
}
